package org.heapbd.evaluation.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.heapbd.evaluation.utility.Constants;

public class MysqlSpecificQueryTest implements Runnable {
	
	private long inputRecordCount;
	private String[] specificFilters;
	private int threadId;
	private Properties properties;
	
	public void setup(long inputRecordCount, String[] specificFilters, int threadId, 
			Properties properties)
	{
		this.inputRecordCount 	= inputRecordCount;
		this.specificFilters 	= specificFilters;
		this.threadId 			= threadId;
		this.properties 		= properties;
	}
	
	public void run()
	{
		// let's give the example of a MySQL implementation.
		String driver 	= properties.getProperty(Constants.DATABASE_DRIVER);
		String url 		= properties.getProperty(Constants.DATABASE_URL);
		String username = properties.getProperty(Constants.DATABASE_USER);
		String password = properties.getProperty(Constants.DATABASE_PASS);
		
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			Statement stmt = conn.createStatement();
			
			String filterTable = loadFiltersToTable(stmt);
			
			String resultTable = runQuery(stmt, filterTable);
			
			cleanUp(stmt, resultTable, filterTable);
			
			stmt.close();
			conn.close();
		} catch (Exception e) {
			System.err.println("Specific Query Test " + threadId + " failed:");
			e.printStackTrace();
		}
	}
	
	protected void cleanUp(Statement stmt, String resultTable, String filterTable)
			throws SQLException
	{
		String cleanup = properties.getProperty(Constants.CLEANUP);
		if (cleanup != null && "true".equals(cleanup))
		{
			stmt.execute("drop table " + resultTable);
			stmt.execute("drop table " + filterTable);
		}
	}
	
	protected String runQuery(Statement stmt, String filterTable) throws SQLException
	{
		String resultTable = "SPECIFIC_FILTER_RESULTS_" +
				inputRecordCount + "_" + threadId + "_" + specificFilters.length;
		String dataTable = "DATA_" + inputRecordCount + "_" + threadId;
		
		stmt.execute("create table " + resultTable + " as " +
				"select d.* " +
				"from " + dataTable + " d " +
				"join " + filterTable + "f on f.FILTER = d.CLIENT_IP ");
		
		return resultTable;
	}
	
	protected String loadFiltersToTable(Statement stmt) throws SQLException
	{
		int specificFilterCount = specificFilters.length;
		
		// load filters first
		String filterTable = "SPECIFIC_FILTER_FILTERS_" + 
				inputRecordCount + "_" + threadId + "_" + specificFilterCount;
		stmt.execute("create table " + filterTable + " ( " +
				"  FILTER varchar(50), " +
				"  index f(FILTER) " +
				") ");
		stmt.execute("alter table + " + filterTable + " disable keys");
		
		int filterCount = 0;
		StringBuilder insertStatement = new StringBuilder();
		insertStatement.append("insert into " + filterTable + " values (");
		for (int x = 0; x < specificFilterCount; x++)
		{
			String filter = specificFilters[x];
			insertStatement.append("('" + filter.replace("'", "''") + "')");
			
			filterCount++;
			
			// we insert up to 10000 filter values at a time
			if (filterCount >= 10000)
			{
				insertStatement.append(")");
				stmt.execute(insertStatement.toString());
				
				insertStatement = new StringBuilder();
				insertStatement.append("insert into " + filterTable + " values (");
				filterCount = 0;
			}
			else if (x < specificFilterCount - 1)
			{
				insertStatement.append(",");
			}
		}
		
		if (filterCount > 0)
		{
			insertStatement.append(")");
			stmt.execute(insertStatement.toString());
		}
		
		return filterTable;
	}
}
